﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace BDM
{
    class GetData
    {
        private static Database db = new Database();

        public static Dictionary<int, string> GetAllPorts()
        {
            DataTable dt = db.GetQuery("SELECT id, naam FROM locaties ORDER BY id ASC");

            Dictionary<int, string> dict = new Dictionary<int, string>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dict.Add((int)dt.Rows[i][0], dt.Rows[i][1].ToString());
            }

            return dict;
        }

        public static Dictionary<int, string> GetPortsPerPort(string PortID)
        {
            DataTable dt = db.GetQuery("SELECT naar FROM trajecten WHERE van=" + PortID + " ORDER BY id ASC");

            string SQL = "SELECT id, naam FROM locaties WHERE ";

            if (dt.Rows.Count < 1)
            {
                return null;
            }

            bool bFirst = true;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (bFirst == true)
                {
                    SQL += "id=" + dt.Rows[i][0].ToString() + " ";
                    bFirst = false;
                }
                else
                {
                    SQL += "OR id=" + dt.Rows[i][0].ToString() + " ";
                }
            }

            SQL += "ORDER BY id ASC";

            DataTable dtData = db.GetQuery(SQL);

            Dictionary<int, string> dict = new Dictionary<int, string>();

            if (dtData.Rows.Count < 1)
            {
                return null;
            }

            for (int i = 0; i < dtData.Rows.Count; i++)
            {
                dict.Add((int)dtData.Rows[i][0], dtData.Rows[i][1].ToString());
            }

            return dict;
        }

        public static String GetPricePerRoute(string VanID, string TotID, bool Spits)
        {
            string sKolom = "prijs";

            if (Spits == true)
            {
                sKolom = "spitsPrijs";
            }

            DataTable dt = db.GetQuery("SELECT " + sKolom + " FROM trajecten WHERE van=" + VanID + " AND naar=" + TotID);

            if (dt.Rows.Count < 1)
            {
                return "0000";
            }
            else
            {
                return dt.Rows[0][0].ToString();
            }
        }

        public static List<string> GetAllPortIDs()
        {
            List<string> list = new List<string>();
            
            DataTable dt = db.GetQuery("SELECT id FROM locaties");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i][0].ToString());
            }

            return list;
        }

        public static DataTable GetTransactions()
        {
            DataTable dt = db.GetQuery("SELECT id AS 'ID', autoId AS 'Auto ID', tijdstip AS 'Datum/Tijd', prijs AS 'Kosten' FROM transacties ORDER BY tijdstip DESC");
            return dt;
        }

        public static DataTable GetFilteredTransactions(string AutoID)
        {
            DataTable dt = db.GetQuery("SELECT id AS 'ID', autoId AS 'Auto ID', tijdstip AS 'Datum/Tijd', prijs AS 'Kosten' FROM transacties WHERE autoId='" + AutoID + "' ORDER BY tijdstip DESC");
            return dt;
        }

        public static DataTable GetAllPortsAsDataTable()
        {
            DataTable dt = db.GetQuery("SELECT id, naam FROM locaties ORDER BY id ASC");
            return dt;
        }

        public static DataTable GetAllClassesAsDataTable()
        {
            DataTable dt = db.GetQuery("SELECT id, prijsfactor FROM autoklasse ORDER BY id ASC");
            return dt;
        }

        public static String GetPortname(string PortID)
        {
            DataTable dt = db.GetQuery("SELECT naam FROM locaties WHERE id = " + PortID);

            if (dt.Rows.Count < 1)
            {
                return "";
            }

            return dt.Rows[0][0].ToString();
        }

        public static String GetCarCaption(bool Spits, string Van, string Naar, string ID)
        {
            //ontvanger|actie|autoId|autoType|vorigePoort
            //05|a|2222|5|03

            string sTarief = "prijs";
            if (Spits == true)
            {
                sTarief = "spitsPrijs";
            }

            DataTable dtPrijs = db.GetQuery("SELECT " + sTarief + " FROM trajecten WHERE van='" + Van + "' AND naar='" + Naar + "'");
            DataTable dtFactor = db.GetQuery("SELECT prijsfactor FROM autoklasse WHERE id='" + ID + "'");
            string sPrijs = "0";

            if (dtPrijs.Rows.Count > 0)
            {
                sPrijs = (Math.Round(Convert.ToDouble(dtPrijs.Rows[0][0].ToString()) * Convert.ToDouble(dtFactor.Rows[0][0].ToString()), 0)).ToString();
            }

            if (sPrijs == "0")
            {
                return "000";
            }
            else
            {
                return sPrijs;
            }
        }

        public static String GetPreparedMessageString(bool Spits, string Text)
        {
            //ontvanger|actie|autoId|autoType|vorigePoort
            //05|a|2222|5|03

            string sTarief = "prijs";
            if (Spits == true)
            {
                sTarief = "spitsPrijs";
            }

            DataTable dtPrijs = db.GetQuery("SELECT " + sTarief + " FROM trajecten WHERE van='" + Text[8].ToString() + Text[9].ToString() + "' AND naar='" + Text[0].ToString() + Text[1].ToString() + "'");
            DataTable dtFactor = db.GetQuery("SELECT prijsfactor FROM autoklasse WHERE id='" + Text[7].ToString() + "'");
            string sPrijs = "0";

            if (dtPrijs.Rows.Count > 0)
            {
                sPrijs = (Math.Round(Convert.ToDouble(dtPrijs.Rows[0][0].ToString()) * Convert.ToDouble(dtFactor.Rows[0][0].ToString()), 0)).ToString();
            }

            if (sPrijs != "0")
            {
                switch (sPrijs.Length)
                {
                    case 0:
                        sPrijs = "0000" + sPrijs;
                        break;
                    case 1:
                        sPrijs = "000" + sPrijs;
                        break;
                    case 2:
                        sPrijs = "00" + sPrijs;
                        break;
                    case 3:
                        sPrijs = "0" + sPrijs;
                        break;
                    default:
                        break;
                }
                string str = "00" + Text[3].ToString() + Text[4].ToString() + Text[5].ToString() + Text[6].ToString() + sPrijs;
                return str;
            }
            return null;
        }

        public static List<string> GetAllCarIDs()
        {
            DataTable dt = db.GetQuery("SELECT DISTINCT autoId from transacties ORDER BY autoId DESC");

            List<string> list = new List<string>();

            if (dt.Rows.Count < 1)
            {
                return list;
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                list.Add(dt.Rows[i][0].ToString());
            }

            return list;
        }
    }
}
